import re
from collections import defaultdict

# 示例输入：SQL查询片段
inputs = [
    "SELECT id, name, age FROM users;",
    "SELECT id, department_id, email FROM employees;",
    "SELECT department_id, department_name FROM departments;"
]

# 正则表达式用于提取表名和字段名
table_pattern = re.compile(r"FROM\s+(\w+);")
field_pattern = re.compile(r"SELECT\s+([^;]+);")

# 提取所有表的字段名
tables_fields = {}
for sql in inputs:
    table_match = table_pattern.search(sql)
    if table_match:
        table_name = table_match.group(1)
        field_match = field_pattern.search(sql)
        if field_match:
            fields = field_match.group(1).split(", ")
            tables_fields[table_name] = fields
print(tables_fields)
        # 找出所有表中共有的字段
common_fields = set(tables_fields[tables_fields.keys()[0]]).intersection(
    *[set(fields) for fields in tables_fields.values()[1:]])

# 构建查询语句
query_parts = []
join_conditions = []
table_aliases = {}
alias_index = 0

for table_name, fields in tables_fields.items():
    alias = f"T{alias_index}"
    table_aliases[table_name] = alias
    alias_index += 1

    # 添加SELECT子句（这里简化为SELECT *，实际中可能需要根据需要选择字段）
    if not query_parts:
        query_parts.append(f"SELECT * FROM {table_name} AS {alias}")
    else:
        # 添加JOIN子句
        for common_field in common_fields:
            if common_field in fields:
                prev_table = query_parts[0].split("FROM")[1].split("AS")[0].strip()
                prev_alias = table_aliases[prev_table]
                join_conditions.append(f"{alias}.{common_field} = {prev_alias}.{common_field}")
        join_on = " AND ".join(join_conditions)
        join_conditions.clear()  # 重置join条件列表，为下一个JOIN做准备
        query_parts.append(f"JOIN {table_name} AS {alias} ON {join_on}")

    # 组装完整的查询语句
final_query = " ".join(query_parts)
print(final_query)

# 注意：这个示例假设至少有两个表需要JOIN，并且第一个表总是作为查询的起点
# 在实际应用中，你可能需要添加更多的逻辑来处理特殊情况，比如只有一个表或没有共同字段的情况